--    Program: 		CAPPOIMP_JOB_B.sql
--    Description:  Import PER JOB information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_job_id					number;
                    		                 		
   cursor cur_per_jobs is
      select * from CRST_PER_JOBS
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_per_jobs loop   	
     l_err_msg_tmp := '';
     
     SELECT PER_JOBS_S.NEXTVAL 
     INTO l_job_id
     FROM DUAL;
     
     update CRST_PER_JOBS
     set    job_id_n = l_job_id
     where  job_id = cur.JOB_ID;
     
     begin
       
      -- import data
       insert into PER_JOBS
       (
            JOB_ID                    ,
            BUSINESS_GROUP_ID         ,
            JOB_DEFINITION_ID         ,
            DATE_FROM                 ,
            COMMENTS                  ,
            DATE_TO                   ,
            NAME                      ,
            REQUEST_ID                ,
            PROGRAM_APPLICATION_ID    ,
            PROGRAM_ID                ,
            PROGRAM_UPDATE_DATE       ,
            ATTRIBUTE_CATEGORY        ,
            ATTRIBUTE1                ,
            ATTRIBUTE2                ,
            ATTRIBUTE3                ,
            ATTRIBUTE4                ,
            ATTRIBUTE5                ,
            ATTRIBUTE6                ,
            ATTRIBUTE7                ,
            ATTRIBUTE8                ,
            ATTRIBUTE9                ,
            ATTRIBUTE10               ,
            ATTRIBUTE11               ,
            ATTRIBUTE12               ,
            ATTRIBUTE13               ,
            ATTRIBUTE14               ,
            ATTRIBUTE15               ,
            ATTRIBUTE16               ,
            ATTRIBUTE17               ,
            ATTRIBUTE18               ,
            ATTRIBUTE19               ,
            ATTRIBUTE20               ,
            LAST_UPDATE_DATE          ,
            LAST_UPDATED_BY           ,
            LAST_UPDATE_LOGIN         ,
            CREATED_BY                ,
            CREATION_DATE             ,
            JOB_INFORMATION_CATEGORY  ,
            JOB_INFORMATION1          ,
            JOB_INFORMATION2          ,
            JOB_INFORMATION3          ,
            JOB_INFORMATION4          ,
            JOB_INFORMATION5          ,
            JOB_INFORMATION6          ,
            JOB_INFORMATION7          ,
            JOB_INFORMATION8          ,
            JOB_INFORMATION9          ,
            JOB_INFORMATION10         ,
            JOB_INFORMATION11         ,
            JOB_INFORMATION12         ,
            JOB_INFORMATION13         ,
            JOB_INFORMATION14         ,
            JOB_INFORMATION15         ,
            OBJECT_VERSION_NUMBER     ,
            JOB_INFORMATION16         ,
            JOB_INFORMATION17         ,
            JOB_INFORMATION18         ,
            JOB_INFORMATION19         ,
            JOB_INFORMATION20         ,
            BENCHMARK_JOB_FLAG        ,
            BENCHMARK_JOB_ID          ,
            EMP_RIGHTS_FLAG           ,
            JOB_GROUP_ID              ,
            APPROVAL_AUTHORITY        
       )
       values
       (
            l_job_id                      ,
            CAPBRIMP_MAPPING.GetBGMapping(cur.BUSINESS_GROUP_ID),
            cur.JOB_DEFINITION_ID         ,
            cur.DATE_FROM                 ,
            cur.COMMENTS                  ,
            cur.DATE_TO                   ,
            cur.NAME                      ,
            cur.REQUEST_ID                ,
            cur.PROGRAM_APPLICATION_ID    ,
            cur.PROGRAM_ID                ,
            cur.PROGRAM_UPDATE_DATE       ,
            cur.ATTRIBUTE_CATEGORY        ,
            cur.ATTRIBUTE1                ,
            cur.ATTRIBUTE2                ,
            cur.ATTRIBUTE3                ,
            cur.ATTRIBUTE4                ,
            cur.ATTRIBUTE5                ,
            cur.ATTRIBUTE6                ,
            cur.ATTRIBUTE7                ,
            cur.ATTRIBUTE8                ,
            cur.ATTRIBUTE9                ,
            cur.ATTRIBUTE10               ,
            cur.ATTRIBUTE11               ,
            cur.ATTRIBUTE12               ,
            cur.ATTRIBUTE13               ,
            cur.ATTRIBUTE14               ,
            cur.ATTRIBUTE15               ,
            cur.ATTRIBUTE16               ,
            cur.ATTRIBUTE17               ,
            cur.ATTRIBUTE18               ,
            cur.ATTRIBUTE19               ,
            cur.ATTRIBUTE20               ,
            cur.LAST_UPDATE_DATE          ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.LAST_UPDATE_LOGIN         ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.CREATION_DATE             ,
            cur.JOB_INFORMATION_CATEGORY  ,
            cur.JOB_INFORMATION1          ,
            cur.JOB_INFORMATION2          ,
            cur.JOB_INFORMATION3          ,
            cur.JOB_INFORMATION4          ,
            cur.JOB_INFORMATION5          ,
            cur.JOB_INFORMATION6          ,
            cur.JOB_INFORMATION7          ,
            cur.JOB_INFORMATION8          ,
            cur.JOB_INFORMATION9          ,
            cur.JOB_INFORMATION10         ,
            cur.JOB_INFORMATION11         ,
            cur.JOB_INFORMATION12         ,
            cur.JOB_INFORMATION13         ,
            cur.JOB_INFORMATION14         ,
            cur.JOB_INFORMATION15         ,
            cur.OBJECT_VERSION_NUMBER     ,
            cur.JOB_INFORMATION16         ,
            cur.JOB_INFORMATION17         ,
            cur.JOB_INFORMATION18         ,
            cur.JOB_INFORMATION19         ,
            cur.JOB_INFORMATION20         ,
            cur.BENCHMARK_JOB_FLAG        ,
            cur.BENCHMARK_JOB_ID          ,
            cur.EMP_RIGHTS_FLAG           ,
            cur.JOB_GROUP_ID              ,
            cur.APPROVAL_AUTHORITY        
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 --DBMS_OUTPUT.PUT_LINE('2');
	 commit;
   else
     --DBMS_OUTPUT.PUT_LINE('1');
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_JOB_B', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   --commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
